import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

// 设备接口
export interface Device {
  id?: number;
  device_id: string;
  name: string;
  classification_name: string;
  classification_code: string;
  specialty: string;
  naming_table_id: string;
  position_table_id: string;
  base_params_table_id: string;
  tech_spec_table_id: string;
  sequence_number: string;
  remarks: string;
  created_at?: string;
  updated_at?: string;
}

// 命名参数接口
export interface NamingParameter {
  id?: number;
  device_id: number;
  sequence?: string;
  parameter_name: string;
  parameter_value: string;
  data_type?: string;
  example_description?: string;
  measurement_class?: string;
  data_source?: string;
  is_required?: boolean;
  created_at?: string;
  updated_at?: string;
}

// 位置参数接口
export interface PositionParameter {
  id?: number;
  device_id: number;
  sequence?: string;
  parameter_name: string;
  parameter_value: string;
  data_type?: string;
  example_description?: string;
  measurement_class?: string;
  data_source?: string;
  is_required?: boolean;
  created_at?: string;
  updated_at?: string;
}

// 基本参数接口
export interface BaseParameter {
  id?: number;
  device_id: number;
  sequence?: string;
  parameter_name: string;
  example_description?: string;
  data_type?: string;
  measurement_class?: string;
  data_source?: string;
  created_at?: string;
  updated_at?: string;
}

export class SqliteDatabase {
  private db: Database.Database | null = null;
  private initialized: boolean = false;

  constructor() {
    this.ensureDataDirectory();
    this.init();
  }

  private init() {
    this.db = new Database(DB_PATH);
    this.db.pragma('journal_mode = WAL');
    this.initialized = true;
  }

  ensureInitialized() {
    if (!this.initialized || !this.db || !this.db.open) {
      this.init();
    }
  }
  
  run(sql: string, params: any[] = []) {
    this.ensureInitialized();
    return this.db!.prepare(sql).run(params);
  }

  get(sql: string, params: any[] = []): any {
    this.ensureInitialized();
    return this.db!.prepare(sql).get(params);
  }

  all(sql: string, params: any[] = []): any[] {
    this.ensureInitialized();
    return this.db!.prepare(sql).all(params);
  }
  
  exec(sql: string) {
    this.ensureInitialized();
    this.db!.exec(sql);
  }

  close() {
    if (this.db && this.db.open) {
      this.db.close();
      this.initialized = false;
    }
  }

  private ensureDataDirectory() {
    const dataDir = path.dirname(DB_PATH);
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }
  }

  // 初始化数据库结构（异步）
  initializeDatabase() {
    this.ensureInitialized();
    this.exec(`
      CREATE TABLE IF NOT EXISTS devices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id TEXT UNIQUE NOT NULL,
        name TEXT NOT NULL,
        specialty TEXT NOT NULL,
        classification_name TEXT,
        classification_code TEXT,
        naming_table_id TEXT,
        position_table_id TEXT,
        base_params_table_id TEXT,
        tech_spec_table_id TEXT,
        sequence_number TEXT,
        remarks TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
      );
      CREATE TABLE IF NOT EXISTS measurement_units (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        category TEXT NOT NULL,
        unit TEXT NOT NULL,
        main_category TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
      );
      CREATE TABLE IF NOT EXISTS naming_parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        sequence TEXT,
        parameter_name TEXT NOT NULL,
        parameter_value TEXT,
        data_type TEXT,
        example_description TEXT,
        measurement_class TEXT,
        data_source TEXT,
        is_required BOOLEAN DEFAULT 0,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
      );
      CREATE TABLE IF NOT EXISTS position_parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        sequence TEXT,
        parameter_name TEXT NOT NULL,
        parameter_value TEXT,
        data_type TEXT,
        example_description TEXT,
        measurement_class TEXT,
        data_source TEXT,
        is_required BOOLEAN DEFAULT 0,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
      );
      CREATE TABLE IF NOT EXISTS base_parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        sequence TEXT,
        parameter_name TEXT NOT NULL,
        example_description TEXT,
        data_type TEXT,
        measurement_class TEXT,
        data_source TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
      );
      CREATE INDEX IF NOT EXISTS idx_devices_specialty ON devices (specialty);
      CREATE INDEX IF NOT EXISTS idx_measurement_units_category ON measurement_units (category);
      CREATE INDEX IF NOT EXISTS idx_naming_parameters_device_id ON naming_parameters (device_id);
      CREATE INDEX IF NOT EXISTS idx_position_parameters_device_id ON position_parameters (device_id);
      CREATE INDEX IF NOT EXISTS idx_base_parameters_device_id ON base_parameters (device_id);
    `);
  }

  // 插入设备
  insertDevice(device: Omit<Device, 'id' | 'created_at' | 'updated_at'>): Device {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const sql = `
      INSERT INTO devices (
        device_id, name, specialty, created_at, updated_at,
        classification_name, classification_code, naming_table_id, position_table_id,
        base_params_table_id, tech_spec_table_id, sequence_number, remarks
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    const params = [
      device.device_id,
      device.name || '未命名设备',
      device.specialty || '配管专业',
      now,
      now,
      device.classification_name || '',
      device.classification_code || '',
      device.naming_table_id || '',
      device.position_table_id || '',
      device.base_params_table_id || '',
      device.tech_spec_table_id || '',
      device.sequence_number || '',
      device.remarks || ''
    ];

    const result = this.run(sql, params);
    const id = result.lastInsertRowid as number;

    return {
      ...device,
      id,
      created_at: now,
      updated_at: now
    };
  }

  // 获取所有设备
  getAllDevices(): Device[] {
    this.ensureInitialized();
    return this.all(`SELECT * FROM devices`);
  }

  // 根据device_id获取设备
  getDeviceByDeviceId(deviceId: string): Device | undefined {
    this.ensureInitialized();
    const sql = `SELECT * FROM devices WHERE device_id = ?`;
    return this.get(sql, [deviceId]) as Device | undefined;
  }

  // 根据专业获取设备
  getDevicesBySpecialty(specialty: string): Device[] {
    this.ensureInitialized();
    return this.all(`SELECT * FROM devices WHERE specialty = ?`, [specialty]);
  }

  // 获取设备及其所有参数
  getDeviceWithParameters(deviceId: number) {
    const device = this.get(`SELECT * FROM devices WHERE id = ?`, [deviceId]) as Device | undefined;
    
    if (!device) return null;

    return {
      device,
      namingParameters: this.all(`SELECT * FROM naming_parameters WHERE device_id = ?`, [deviceId]) as NamingParameter[],
      positionParameters: this.all(`SELECT * FROM position_parameters WHERE device_id = ?`, [deviceId]) as PositionParameter[],
      baseParameters: this.all(`SELECT * FROM base_parameters WHERE device_id = ?`, [deviceId]) as BaseParameter[]
    };
  }

  // 更新设备
  updateDevice(deviceId: number, updates: Partial<Device>): Device | null {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const updateFields = Object.keys(updates).map(key => `${key} = ?`).join(', ');
    const values = Object.values(updates);
    const result = this.run(`
      UPDATE devices 
      SET ${updateFields}, updated_at = ?
      WHERE id = ?
    `, [...values, now, deviceId]);
    if (result.changes > 0) {
      return this.get(`SELECT * FROM devices WHERE id = ?`, [deviceId]) as Device;
    }
    return null;
  }

  // 新增：清空所有主要表
  clearDatabase() {
    this.ensureInitialized();
    this.exec(`
      DELETE FROM devices;
      DELETE FROM naming_parameters;
      DELETE FROM position_parameters;
      DELETE FROM base_parameters;
    `);
  }

  // 新增：获取仪表板统计信息
  getDashboardStats() {
    const stmt = this.db!.prepare(
      "SELECT specialty, COUNT(*) as count FROM devices GROUP BY specialty ORDER BY count DESC"
    );
    return stmt.all();
  }

  // 计量单位相关方法
  insertMeasurementUnit(unit: {
    category_name: string;
    unit_name: string;
    unit_symbol?: string;
    unit_type?: string;
    conversion_factor?: number;
    base_unit?: string;
    description?: string;
    is_active?: boolean;
    sort_order?: number;
  }) {
    const now = new Date().toISOString();
    const stmt = this.db!.prepare(`
      INSERT OR IGNORE INTO measurement_units (
        category_name, unit_name, unit_symbol, unit_type,
        conversion_factor, base_unit, description, is_active,
        sort_order, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const result = stmt.run(
      unit.category_name,
      unit.unit_name,
      unit.unit_symbol || unit.unit_name,
      unit.unit_type || '常用单位',
      unit.conversion_factor || 1.0,
      unit.base_unit || unit.unit_name,
      unit.description || '',
      unit.is_active !== false ? 1 : 0,
      unit.sort_order || 0,
      now,
      now
    );

    return {
      id: result.lastInsertRowid as number,
      ...unit,
      unit_symbol: unit.unit_symbol || unit.unit_name,
      unit_type: unit.unit_type || '常用单位',
      conversion_factor: unit.conversion_factor || 1.0,
      base_unit: unit.base_unit || unit.unit_name,
      description: unit.description || '',
      is_active: unit.is_active !== false,
      sort_order: unit.sort_order || 0,
      created_at: now,
      updated_at: now
    };
  }

  getAllMeasurementUnits() {
    this.ensureInitialized();
    return this.all('SELECT * FROM measurement_units WHERE is_active = 1 ORDER BY category_name, sort_order, unit_name');
  }

  getMeasurementUnitsByCategory(category: string) {
    const stmt = this.db!.prepare('SELECT * FROM measurement_units WHERE category_name = ? AND is_active = 1 ORDER BY sort_order, unit_name');
    return stmt.all(category);
  }

  getMeasurementUnitById(id: number) {
    const stmt = this.db!.prepare('SELECT * FROM measurement_units WHERE id = ?');
    return stmt.get(id);
  }

  updateMeasurementUnit(id: number, updates: Partial<{
    category_name: string;
    unit_name: string;
    unit_symbol: string;
    unit_type: string;
    conversion_factor: number;
    base_unit: string;
    description: string;
    is_active: boolean;
    sort_order: number;
  }>) {
    const now = new Date().toISOString();
    const updateFields = Object.keys(updates).map(key => `${key} = ?`).join(', ');
    const values = Object.values(updates);

    const stmt = this.db!.prepare(`
      UPDATE measurement_units
      SET ${updateFields}, updated_at = ?
      WHERE id = ?
    `);
    return stmt.run(...values, now, id);
  }

  deleteMeasurementUnit(id: number) {
    const stmt = this.db!.prepare('DELETE FROM measurement_units WHERE id = ?');
    return stmt.run(id);
  }

  clearMeasurementUnits() {
    const stmt = this.db!.prepare('DELETE FROM measurement_units');
    const result = stmt.run();
    // 重置自增ID
    this.db!.prepare("DELETE FROM sqlite_sequence WHERE name='measurement_units'").run();
    return result;
  }

  // 获取计量单位分类列表
  getMeasurementUnitCategories() {
    this.ensureInitialized();
    return this.all('SELECT DISTINCT category_name FROM measurement_units WHERE is_active = 1 ORDER BY category_name');
  }

  // 插入命名参数
  insertNamingParameter(param: Omit<NamingParameter, 'id' | 'created_at' | 'updated_at'>): NamingParameter {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const result = this.run(`
      INSERT INTO naming_parameters (device_id, sequence, parameter_name, parameter_value, data_type, example_description, measurement_class, data_source, is_required, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      param.device_id,
      param.sequence || "",
      param.parameter_name,
      param.parameter_value || "",
      param.data_type,
      param.example_description,
      param.measurement_class,
      param.data_source,
      param.is_required ? 1 : 0,
      now,
      now
    ]);
    return {
      ...param,
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now
    };
  }

  // 插入位置参数
  insertPositionParameter(param: Omit<PositionParameter, 'id' | 'created_at' | 'updated_at'>): PositionParameter {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const result = this.run(`
      INSERT INTO position_parameters (device_id, sequence, parameter_name, parameter_value, data_type, example_description, measurement_class, data_source, is_required, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      param.device_id,
      param.sequence || "",
      param.parameter_name,
      param.parameter_value || "",
      param.data_type,
      param.example_description,
      param.measurement_class,
      param.data_source,
      param.is_required ? 1 : 0,
      now,
      now
    ]);
    return {
      ...param,
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now
    };
  }

  // 插入基础参数
  insertBaseParameter(param: Omit<BaseParameter, 'id' | 'created_at' | 'updated_at'>): BaseParameter {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const result = this.run(`
      INSERT INTO base_parameters (device_id, sequence, parameter_name, example_description, data_type, measurement_class, data_source, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      param.device_id,
      param.sequence || "",
      param.parameter_name,
      param.example_description || "",
      param.data_type,
      param.measurement_class || "",
      param.data_source || "",
      now,
      now
    ]);
    return {
      ...param,
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now
    };
  }

  // 查询命名参数
  getNamingParameters(deviceId: number): NamingParameter[] {
    this.ensureInitialized();
    return this.all(`SELECT * FROM naming_parameters WHERE device_id = ?`, [deviceId]);
  }

  // 查询位置参数
  getPositionParameters(deviceId: number): PositionParameter[] {
    this.ensureInitialized();
    return this.all(`SELECT * FROM position_parameters WHERE device_id = ?`, [deviceId]);
  }

  // 查询基础参数
  getBaseParameters(deviceId: number): BaseParameter[] {
    this.ensureInitialized();
    return this.all(`SELECT * FROM base_parameters WHERE device_id = ?`, [deviceId]);
  }

  // 删除命名参数
  deleteNamingParameters(deviceId: number): void {
    this.ensureInitialized();
    this.run(`DELETE FROM naming_parameters WHERE device_id = ?`, [deviceId]);
  }

  // 删除位置参数
  deletePositionParameters(deviceId: number): void {
    this.ensureInitialized();
    this.run(`DELETE FROM position_parameters WHERE device_id = ?`, [deviceId]);
  }

  // 删除基础参数
  deleteBaseParameters(deviceId: number): void {
    this.ensureInitialized();
    this.run(`DELETE FROM base_parameters WHERE device_id = ?`, [deviceId]);
  }

  // 删除设备（包括相关参数）
  deleteDevice(deviceId: string): boolean {
    this.ensureInitialized();
    
    console.log(`[SQL] 开始删除设备: ${deviceId}`);
    
    // 通过device_id查找设备
    const device = this.getDeviceByDeviceId(deviceId);
    if (!device || !device.id) {
      console.log(`[SQL] 设备 ${deviceId} 不存在`);
      return false;
    }
    
    const internalId = device.id;
    
    // 删除相关参数
    console.log(`[SQL] DELETE FROM naming_parameters WHERE device_id = ${internalId}`);
    this.run(`DELETE FROM naming_parameters WHERE device_id = ?`, [internalId]);
    
    console.log(`[SQL] DELETE FROM position_parameters WHERE device_id = ${internalId}`);
    this.run(`DELETE FROM position_parameters WHERE device_id = ?`, [internalId]);
    
    console.log(`[SQL] DELETE FROM base_parameters WHERE device_id = ${internalId}`);
    this.run(`DELETE FROM base_parameters WHERE device_id = ?`, [internalId]);
    
    // 删除设备本身
    console.log(`[SQL] DELETE FROM devices WHERE id = ${internalId}`);
    const result = this.run(`DELETE FROM devices WHERE id = ?`, [internalId]);
    
    const success = result.changes > 0;
    console.log(`[SQL] 设备删除${success ? '成功' : '失败'}，影响行数: ${result.changes}`);
    
    return success;
  }

  // 更新命名参数值
  updateNamingParameterValue(parameterId: number, parameterValue: string): NamingParameter | null {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const result = this.run(`
      UPDATE naming_parameters 
      SET parameter_value = ?, updated_at = ?
      WHERE id = ?
    `, [parameterValue, now, parameterId]);
    if (result.changes > 0) {
      return this.get(`SELECT * FROM naming_parameters WHERE id = ?`, [parameterId]) as NamingParameter;
    }
    return null;
  }

  // 更新位置参数值
  updatePositionParameterValue(parameterId: number, parameterValue: string): PositionParameter | null {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const result = this.run(`
      UPDATE position_parameters 
      SET parameter_value = ?, updated_at = ?
      WHERE id = ?
    `, [parameterValue, now, parameterId]);
    if (result.changes > 0) {
      return this.get(`SELECT * FROM position_parameters WHERE id = ?`, [parameterId]) as PositionParameter;
    }
    return null;
  }

  // 更新基础参数值
  updateBaseParameterValue(parameterId: number, parameterValue: string): BaseParameter | null {
    this.ensureInitialized();
    const now = new Date().toISOString();
    const result = this.run(`
      UPDATE base_parameters 
      SET parameter_value = ?, updated_at = ?
      WHERE id = ?
    `, [parameterValue, now, parameterId]);
    if (result.changes > 0) {
      return this.get(`SELECT * FROM base_parameters WHERE id = ?`, [parameterId]) as BaseParameter;
    }
    return null;
  }

  prepare(sql: string): Database.Statement {
    this.ensureInitialized();
    return this.db!.prepare(sql);
  }

  transaction(fn: (...args: any[]) => any): (...args: any[]) => any {
    this.ensureInitialized();
    return this.db!.transaction(fn);
  }
}

let dbInstance: SqliteDatabase | null = null;

export function getDbInstance(): SqliteDatabase {
  if (!dbInstance) {
    dbInstance = new SqliteDatabase();
  }
  return dbInstance;
}

// Export a singleton instance for backwards compatibility
export const sqliteDb = getDbInstance();